#!/usr/bin/env python
#
#   DictDB
#
#
import sqlite3

class EmptyKeyError(Exception):
    ''' raise excetion when key is None or empty '''
    pass

class EmptyValueError(Exception):
    ''' raise excetion when value is None or empty '''
    pass

class DuplicateKeyValueError(Exception):
    ''' raise excetion when duplicate key/value '''
    pass

class DictDB():
    '''
    DictDB, key-value database
    
    data stored in sqlite3 database
    '''
    def __init__(self, db, fields={}):
        '''
        init
        '''
        self.__conn = sqlite3.connect(db)
        self.__cursor = self.__conn.cursor()
        
        self.__fields = fields
        self.__cursor.execute("CREATE TABLE IF NOT EXISTS entity (id INTEGER PRIMARY KEY, n NONE)")
        for f_k in fields:
            f_type = 'TEXT'
            f_index = ''
            if 'type' in fields[f_k].keys():
                if fields[f_k]['type'] == 'text': f_type = 'TEXT'
                if fields[f_k]['type'] == 'integer': f_type = 'INTEGER'
            if 'index' in fields[f_k].keys():
                if fields[f_k]['index'] == 'unique': f_index = ' UNIQUE '
            
            SQL = '''CREATE TABLE IF NOT EXISTS %s_data 
                    (id INTEGER NOT NULL, value %s NOT NULL %s);
                    
                    CREATE UNIQUE INDEX 
                    IF NOT EXISTS %s_id_index ON %s_data (id);''' % (f_k, f_type, f_index, f_k, f_k)
            self.__cursor.executescript(SQL)
                    
    def add_entity(self):
        '''
        add entity with key/value pairs
        '''
        entity_id = None
        SQL = "INSERT INTO entity (n) VALUES ('');"
        try:
            self.__cursor.execute(SQL)
            entity_id = self.__cursor.lastrowid
        except sqlite3.IntegrityError:
            raise DiplicateKeyValueError()
        return entity_id
        
    def add_key_value(self, entity_id, key, value):
        '''
        add pairs of key/value for instance
        '''
        if value:
            SQL = "INSERT INTO %s_data (id, value) VALUES (%d, '%s')" % (key, entity_id, value)
            try:
                self.__cursor.execute(SQL)
                key_value_id = self.__cursor.lastrowid
            except sqlite3.IntegrityError:
                raise DuplicateKeyValueError
            return key_value_id


    def delete_entity(self, entity_id):
        '''
        delete entity with key/value pairs
        '''
        SQL = "DELETE FROM entity WHERE id = %d" % entity_id
        self.__cursor.execute(SQL)
        
    def delete(self, entity_id, key):
        '''
        delete pair by key
        '''
        if key:
            SQL = "DELETE FROM %s_data WHERE id=%d" % (key, entity_id)
            self.__cursor.execute(SQL)
        else:
            raise EmptyKeyError()

    def select(self, keys=[], where=None, limit=0):
        '''
        select data by keys
        '''
        data = []
        fields = ''
        tables = ''
        for k in keys:
            fields += ", %s_data.value AS %s" % (k, k)
            tables += " LEFT JOIN %s_data ON (entity.id=%s_data.id)" % (k, k)
        SQL = "SELECT entity.id AS id %s FROM entity %s" % (fields, tables)
        if where: SQL += " WHERE %s" % where
        if limit > 0: SQL += " LIMIT %d" % limit
        self.__cursor.execute(SQL)
        for row in self.__cursor:
            data.append(row)
        return data

    def sql(self, sql):
        '''
        exceute sql
        '''
        result = []
        self.__cursor.execute(sql)
        for row in self.__cursor:
            result.append(row)
        return result

    def sync(self):
        '''
        sync data wyth disk: commit transactions
        '''
        self.__conn.commit()
        
    def vacuum(self):
        '''
        sqlite3 command: vacuum
        '''        
        self.__cursor.execute("VACUUM")

if __name__ == "__main__":

    from optparse import OptionParser
    import sys
	
    parser = OptionParser()
    parser.add_option("-d", "--db", dest="db", help="database file")
    (options, args) = parser.parse_args()

    if options.db == None:
        parser.error("Incorrect number of arguments, \n\nuse -h or --help for detail\n")
        sys.exit()
    
    db = DictDB(options.db)
    db.vacuum()
